use axum::{Json, extract::Query};
use com::{ code, query, sea_orm::connect_db };
use sea_orm::{query::*, DatabaseConnection, DbBackend, FromQueryResult};
use serde::{ Deserialize, Serialize };
use serde_json::json;
#[derive(Debug, Deserialize, Serialize, FromQueryResult)]
pub struct Data {}

pub async fn list_(param:&JsonValue, all:bool) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let table_name = query::str_op(param,"table_name")?;
    let dbname = query::str(param,"database")?;
    let sort = query::str_op(param,"sort")?;
    let sort_type = query::str_op(param,"sort_type")?;
    let sorter = if let Some(f) = sort { format!("order by {} {}", f, sort_type.unwrap()) } else { "order by create_time desc".to_owned() };
    let conn = connect_db(&dbname).await?;
    let table_name_sql = if let Some(f) = table_name { format!(" and table_name like '%{}%'", f) } else { "".to_owned() };
    let stmt = Statement::from_sql_and_values(
        conn.get_database_backend(),
        [
            "select 
                table_name as table_name, 
                round(( data_length + index_length) / 1024 / 1024, 2) as 'Size(MB)',
                table_rows as table_rows,
                data_free as data_free,
                row_format as row_format,
                table_catalog as table_catalog,
                table_schema as table_schema,
                table_name as table_name,
                table_type as table_type,
                engine as engine,
                version as version,
                avg_row_length as avg_row_length,
                data_length as data_length,
                index_length as index_length,
                max_data_length as max_data_length,
                auto_increment as auto_increment,
                
                table_collation as table_collation,
                checksum as checksum,
                create_options as create_options,
                table_comment as table_comment,

                date_format(convert_tz(create_time, '+00:00', '+08:00' ),'%Y-%m-%d %H:%i:%s') as create_time,
                date_format(convert_tz(update_time, '+00:00', '+08:00' ),'%Y-%m-%d %H:%i:%s') as update_time,
                date_format(convert_tz(check_time, '+00:00', '+08:00' ),'%Y-%m-%d %H:%i:%s') as check_time 
            ",
            "from information_schema.tables",
            &format!("where table_schema = '{}'", dbname),
            &table_name_sql,
            if all {""} else { " and table_name!='seaql_migrations'" },
            &sorter
        ].join(" "),
        []
    );
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
pub async fn list(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    list_(&param, false).await
}
pub async fn list_all(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    list_(&param, true).await
}
pub async fn dict(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let conn = connect_db(&dbname).await?;
    let stmt = Statement::from_sql_and_values(
        conn.get_database_backend(),
        [
            "select table_name as label",
            " from information_schema.tables",
            &format!(" where table_schema='{}'", dbname),
            " and table_name!='seaql_migrations'",
            " order by create_time desc"
        ].join(" "),
        []
    );
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
pub async fn col(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let conn = connect_db(&dbname).await?;
    let table_name = query::str(&param,"table_name")?;
    // 获取表列
    let stmt = Statement::from_sql_and_values(
        DbBackend::MySql, 
        "select column_name as col from information_schema.columns where table_name=? and table_schema=?",
        [table_name.into(), dbname.into()]
    );
    let result = JsonValue::find_by_statement(stmt).into_json().all(&conn).await;
    code::send_arr( result )
}
// 查看表详情
pub async fn show_columns(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!(r#"
    select COLUMN_NAME, 
    CAST(COLUMN_TYPE as CHAR) as 'COLUMN_TYPE',
    CAST(DATA_TYPE as CHAR) as 'DATA_TYPE',
    IS_NULLABLE,
    CAST(COLUMN_DEFAULT as CHAR) as 'COLUMN_DEFAULT',
    CAST(COLUMN_KEY as CHAR) as 'COLUMN_KEY',
    EXTRA,
    CAST(COLUMN_COMMENT as CHAR) as 'COLUMN_COMMENT'
    from information_schema.columns where table_name='{}' and table_schema='{}' ORDER BY ORDINAL_POSITION"#, table_name, dbname);
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
// 查看主键
pub async fn show_key(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!("show index from {}", table_name);
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
pub async fn find_key_name(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let column_name = query::str(&param,"column_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!("show indexes from {} where Column_name='{}'", table_name, column_name);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
pub async fn find_primary_key(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!("select * from information_schema.key_column_usage {} where Constraint='PRIMARY'", table_name);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_op_obj( Data::find_by_statement(stmt).into_model().one(&conn).await )
}
pub async fn show_foreign_key(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!(r#"
        select * from information_schema.key_column_usage 
        where table_schema='{}' and table_name='{}' and REFERENCED_COLUMN_NAME is not null"#
    , dbname, table_name);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
pub async fn show_foreign_keys(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!(r#"
        select * from information_schema.key_column_usage 
        where table_schema='{}' and REFERENCED_COLUMN_NAME is not null"#
    , dbname);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
// 表结构
pub async fn table_structure(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    // let mut arr:Vec<String> = Vec::new();
    // if let Some(f) = query::str_op(&param,"col_name")? { 
    //     arr.push(format!("Field like '%{}%'", f))
    // };
    // if let Some(f) = query::str_op(&param,"type")? { 
    //     arr.push(format!("Type like '%{}%'", f))
    // };
    // let sq = if arr.is_empty(){ "".to_owned() } else { format!(" where {}", arr.join(" and ")) };
    let conn = connect_db(&dbname).await?;
    // let sql = format!("show columns from {}{}", table_name,sq);
    let sql = format!(r#"
        SELECT COLUMN_NAME as 'Field',
        CAST(COLUMN_TYPE as CHAR) as 'Type',
        IS_NULLABLE as 'Null',
        CAST(COLUMN_DEFAULT as CHAR) as 'Default',
        Extra as 'Extra',
        CAST(COLUMN_COMMENT as CHAR) as 'Comment',
        CAST(COLUMN_KEY as CHAR) as 'Key'
        FROM information_schema.columns
        WHERE table_schema='{}' AND TABLE_NAME='{}' ORDER BY ORDINAL_POSITION
    "#, dbname, table_name);

    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
//
pub async fn show_information_schema_table(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!(r#"
        select
            table_name as table_name, 
            round(( data_length + index_length) / 1024 / 1024, 2) as 'Size(MB)',
            table_rows as table_rows,
            data_free as data_free,
            CAST(row_format as char) as row_format,
            table_catalog as table_catalog,
            table_schema as table_schema,
            table_name as table_name,
            CAST(table_type as char) as table_type,
            engine as engine,
            version as version,
            avg_row_length as avg_row_length,
            data_length as data_length,
            index_length as index_length,
            max_data_length as max_data_length,
            auto_increment as auto_increment,
            
            table_collation as table_collation,
            checksum as checksum,
            create_options as create_options,
            table_comment as table_comment,

            date_format(convert_tz(create_time, '+00:00', '+08:00' ),'%Y-%m-%d %H:%i:%s') as create_time,
            date_format(convert_tz(update_time, '+00:00', '+08:00' ),'%Y-%m-%d %H:%i:%s') as update_time,
            date_format(convert_tz(check_time, '+00:00', '+08:00' ),'%Y-%m-%d %H:%i:%s') as check_time 
        from information_schema.tables 
        where table_schema='{}' and table_name='{}'"#
    , dbname, table_name);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
// 查询建表 SQL
pub async fn show_create_table_sql(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let conn = connect_db(&dbname).await?;
    let sql  = format!("show create table {}", table_name);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_op_obj( Data::find_by_statement(stmt).into_model().one(&conn).await )
}
// 判断表是否存在
pub async fn table_exist(conn:&DatabaseConnection, database:&str, table_name:&str) -> Result<bool, Json<JsonValue>>{
    let sql  = format!(r#"
        select if(
            exists( select 1 from information_schema.tables where table_schema = '{}' and table_name = '{}' ),
            true,
            false
        ) as result;
    "#, database, table_name);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    match Data::find_by_statement(stmt).into_json().one(conn).await {
        Ok(f) => {
            if let Some(d) = f {
                Ok(query::bool(&d, "result")?)
            }else{
                Err(code::sys_("查询值不存在"))
            }
        },
        Err(e) => Err(code::sys_(e.to_string()))
    }
}
// 查询表数据
pub async fn table_data(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;

    let sort = query::str_op(&param,"sort")?;
    let sort_type = query::str_op(&param,"sort_type")?;
    let sorter = if let Some(f) = sort { format!(" order by {} {}", f, sort_type.unwrap()) } else { "".to_owned() };

    let page = query::i32_op(&param,"page")?.unwrap_or(1);
    let size = query::i32_op(&param,"size")?.unwrap_or(10);
    let offset = size * (page-1);

    let conn = connect_db(&dbname).await?;
    // 总条数
    let stmt = Statement::from_string(
        DbBackend::MySql,
        format!("select count(*) as total_items from {}", table_name)
    );
    let result = Data::find_by_statement(stmt).into_json().one(&conn).await.unwrap_or_default();
    let total_items = result.map_or_else(||0, |f| query::i32(&f,"total_items").unwrap_or(1));
    let total_pages = ( total_items as f32 ) / (size as f32);
    let total_pages = total_pages.ceil() as i32;
    // 查询数据
    let sql = format!("select * from {}{} limit {} offset {}", table_name, &sorter, size, offset);
    let stmt = Statement::from_string( DbBackend::MySql,sql );
    let result = Data::find_by_statement(stmt).into_json().all(&conn).await.unwrap_or_default();
    code::data(json!({ "data": result, "total_items": total_items, "total_pages": total_pages, "page": page, "size": size }))
}
pub async fn table_data_find(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let table_name = query::str(&param,"table_name")?;
    let id = query::i32(&param,"id")?;
    let conn = connect_db(&dbname).await?;
    let sql = format!("select * from {} where id={}", table_name, id);
    let stmt = Statement::from_string(DbBackend::MySql,sql);
    code::send_op_obj( Data::find_by_statement(stmt).into_model().one(&conn).await )
}